<?php

namespace RedMVC\Db;

/*

db table create:

CREATE TABLE `closure_table_name` (
 `parentId` bigint(20) unsigned NOT NULL,
 `childId` bigint(20) unsigned NOT NULL,
 `treeDepth` int(11) unsigned NOT NULL,
 PRIMARY KEY (`parentId`,`childId`),
 KEY `treeDepth` (`treeDepth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

*/

/**
 * ClosureTable class
 *  
 * @category    RedMVC
 * @package     Db
 * @author      Jan Fischer, bitWorking <info@bitworking.de>
 */
class ClosureTable{

    protected $_db;
    protected $_closureTableName;
    protected $_parentTableName;
    protected $_parentTablePrimary  = 'id';
    protected $_parentTableOrder    = 'sort';
    
    public function __set($name, $value){
        $method = 'set'.ucfirst($name);
        if(!method_exists($this, $method)){
            throw new \Exception('ClosureTable: Invalid property');
        }
        $this->$method($value);
    }
  
    public function __get($name){
        $method = 'get'.ucfirst($name);
        if(!method_exists($this, $method)){
            throw new \Exception('ClosureTable: Invalid property');
        }
        return $this->$method();
    }
  
    public function setDb($db){
        $this->_db = $db;
        return $this;
    }
  
    public function getDb(){
        return $this->_db;
    }
  
    public function setClosureTableName($closureTableName){
        $this->_closureTableName = $closureTableName;
        return $this;
    }
  
    public function getClosureTableName(){
        return $this->_closureTableName;
    }
  
    public function setParentTableName($parentTableName){
        $this->_parentTableName = $parentTableName;
        return $this;
    }
  
    public function getParentTableName(){
        return $this->_parentTableName;
    }
  
    public function setParentTablePrimary($parentTablePrimary){
        $this->_parentTablePrimary = $parentTablePrimary;
        return $this;
    }
  
    public function getParentTablePrimary(){
        return $this->_parentTablePrimary;
    }
  
    public function setParentTableOrder($parentTableOrder){
        $this->_parentTableOrder = $parentTableOrder;
        return $this;
    }
  
    public function getParentTableOrder(){
        return $this->_parentTableOrder;
    }
  
    public function setup($db, $closureTableName, $parentTableName,
                          $parentTablePrimary = 'id', $parentTableOrder = 'sort'){
        $this->setDb($db);
        $this->setClosureTableName($closureTableName);
        $this->setParentTableName($parentTableName);
        $this->setParentTablePrimary($parentTablePrimary);
        $this->setParentTableOrder($parentTableOrder);
    }
  
    public function addRoot($labelField){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $db       = $this->getDb();
        $stmt     = $db->query(
            "INSERT INTO $tParent($tPrimary, $labelField) VALUES(1, 'root')"
        );    
        $stmt     = $db->query(
            "INSERT INTO $tTree(parentId, childId, treeDepth) VALUES(1, 1, 0)"
        );
    }
  
    public function byId($id){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $db       = $this->getDb();    
        $row      = $db->fetchRow(
            "SELECT p.*, t2.parentId
            FROM $tParent AS p
            LEFT JOIN $tTree AS t2 ON (p.$tPrimary = t2.childId AND t2.treeDepth=1)
            WHERE p.$tPrimary = ? LIMIT 1",
            array($id)
        );        
        return $row;
    }
  
    public function save($id, $parentId){
        $tTree    = $this->getClosureTableName();
        $db       = $this->getDb();    
        $stmt     = $db->query(
            "INSERT INTO $tTree (parentId, childId, treeDepth)
              SELECT t.parentId, ?, t.treeDepth+1
              FROM $tTree AS t
              WHERE t.childId = ?
              UNION ALL
              SELECT ?, ?, 0",
            array($id, $parentId, $id, $id)
        );
    }
  
    public function update($id, $parentId){
        $tTree    = $this->getClosureTableName();
        $db       = $this->getDb();
        
        if($db->getType() == C::TYPE_MYSQL){
            $this->updateMysql($id, $parentId);
            return;
        }
  
        // delete
        $stmt = $db->query(
            "DELETE FROM $tTree
              WHERE childId IN(
                SELECT childId FROM
                (SELECT childId FROM $tTree WHERE parentId = ?)
                AS TEMPTABLE
              )
              AND parentId IN(
                SELECT parentId FROM
                (SELECT parentId
                FROM $tTree
                WHERE childId = ?
                AND parentId != childId)
                AS TEMPTABLE
              )",
            array($id, $id)
        );
    
        // insert
        $stmt = $db->query(
            "INSERT INTO $tTree (parentId, childId, treeDepth)(
              SELECT supertree.parentId, subtree.childId, (supertree.treedepth+subtree.treeDepth)+1
              FROM $tTree AS supertree
              CROSS JOIN $tTree AS subtree
              WHERE supertree.childId = ?
              AND subtree.parentId = ?
            )",
            array($parentId, $id)
        );
    }
    
    public function updateMysql($id, $parentId){
        $tTree    = $this->getClosureTableName();
        $db       = $this->getDb();
 
        // delete        
        $stmt = $db->query(
            "DELETE t FROM $tTree AS t 
            NATURAL JOIN(
              SELECT t2.parentId, t3.childId
              FROM $tTree AS t2
              CROSS JOIN $tTree AS t3
              WHERE t2.childId = ? 
              AND t2.parentId != ? 
              AND t3.parentId = ?
            ) AS t4",
            array($id, $id, $id)
        );
    
        // insert
        $stmt = $db->query(
            "INSERT INTO $tTree (parentId, childId, treeDepth)(
              SELECT supertree.parentId, subtree.childId, (supertree.treedepth+subtree.treeDepth)+1
              FROM $tTree AS supertree
              CROSS JOIN $tTree AS subtree
              WHERE supertree.childId = ?
              AND subtree.parentId = ?
            )",
            array($parentId, $id)
        );
    }
  
    public function addReference($id, $parentId){
        $tTree    = $this->getClosureTableName();
        $db       = $this->getDb();
    
        // insert
        $stmt = $db->query(
            "INSERT INTO $tTree (parentId, childId, treeDepth) 
            VALUES(?, ?, 1)
            ",
            array($parentId, $id)
        );
    }
  
    public function delete($id, $deleteFromParent = false){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $db       = $this->getDb();
        
        $stmt     = $db->query('SET foreign_key_checks = 0');
        $stmt     = $db->query(
            "DELETE FROM $tTree where childId = ?",
            array($id)
        );
        if($deleteFromParent){
            $stmt = $db->query(
                "DELETE FROM $tParent where $tPrimary = ?",
                array($id)
            );
        }
        $stmt = $db->query('SET foreign_key_checks = 1');
    }
        
    public function deleteSubtree($id, $deleteFromParent = false){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $tOrder   = $this->getParentTableOrder();
        $db       = $this->getDb();
        
        if($db->getType() == C::TYPE_MYSQL){
            $this->deleteSubtreeMysql($id, $deleteFromParent);
            return;
        }

        $stmt = $db->query('SET foreign_key_checks = 0');
        if($deleteFromParent){
            $stmt = $db->query(
                "DELETE FROM $tParent
                WHERE $tPrimary IN(
                    SELECT childId FROM (SELECT childId FROM $tTree WHERE parentId = ?) AS TEMPTABLE
                )",
                array($id)
            );
        }
        $stmt = $db->query(
            "DELETE FROM $tTree
            WHERE childId IN(
                SELECT childId FROM (SELECT childId FROM $tTree WHERE parentId = ?) AS TEMPTABLE
            )",
            array($id)
        );
        $stmt = $db->query('SET foreign_key_checks = 1');
    }

    public function deleteSubtreeMysql($id, $deleteFromParent = false){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $tOrder   = $this->getParentTableOrder();
        $db       = $this->getDb();

        $stmt = $db->query('SET foreign_key_checks = 0');
        if($deleteFromParent){
            $stmt = $db->query(
                "DELETE p FROM $tParent AS p
                JOIN $tTree AS t ON (p.$tPrimary = t.childId) 
                WHERE t.parentId = ?
                ",
                array($id)
            );
        }
        $stmt = $db->query(
            "DELETE t FROM $tTree AS t 
            JOIN $tTree AS t2 USING (childId) 
            WHERE t2.parentId = ?
            ",
            array($id)
        );
        $stmt = $db->query('SET foreign_key_checks = 1');
    }
    
    public function getChilds($id, $includeSelf = false, $whereClauses = array(), $maxTreeDepth = null){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $tOrder   = $this->getParentTableOrder();
        $db       = $this->getDb();
    
        $whereSql = "";
        foreach($whereClauses as $whereClause){
          if(is_array($whereClause) && count($whereClause) == 3){
              $whereSql .= "AND p2.".$whereClause[0]." ".$whereClause[1]." ".$whereClause[2]." ";
          }
        }
    
        $treeDepthSql = '';
        if(null !== $maxTreeDepth){
            $maxTreeDepth = (int)$maxTreeDepth + 1;
            $treeDepthSql = "AND t.treeDepth < $maxTreeDepth ";
        }
        
        $includeSelfSql = '';
        if(!$includeSelf){
            $includeSelfSql = 'AND t.treeDepth > 0';
        }
    
        $rows = $db->fetch(
            "SELECT p2.*, t2.parentId, t.treeDepth 
            FROM $tParent AS p
            JOIN $tTree AS t ON (t.parentId = p.$tPrimary)
            JOIN $tParent AS p2 ON (t.childId = p2.$tPrimary)
            LEFT OUTER JOIN $tTree AS t2 ON (t2.childId = p2.$tPrimary AND t2.treeDepth = 1)
            WHERE p.$tPrimary = ? $includeSelfSql 
            $whereSql
            $treeDepthSql
            ORDER BY t.treeDepth, p2.$tOrder, p2.$tPrimary",
            array($id)
        );
        return $rows;
    }
    
    public function getFirstChilds($id, $includeSelf = false, $whereClauses = array()){
        return $this->getChilds($id, $includeSelf, $whereClauses, 1);
    }
    
    public function getChildsPath($id, $labelField){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $tOrder   = $this->getParentTableOrder();
        $db       = $this->getDb();

        $rows = $db->fetch(
            "SELECT
            t.childId as $tPrimary,
            GROUP_CONCAT(p.$labelField ORDER BY t.treeDepth, p.$tPrimary separator '/') AS $labelField,
            t.treeDepth
            FROM $tTree t
            JOIN $tTree t2 ON (t2.childId = t.childId)
            JOIN $tParent p ON (p.$tPrimary = t2.parentId)
            WHERE t.parentId = ?  
            AND t.childId != t.parentId
            GROUP BY t.childId",
            array($id)
        );        
        return $rows;
    }
  
    public function getParents($id, $includeSelf = false, $sort = C::SORT_DESC){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $tOrder   = $this->getParentTableOrder();
        $db       = $this->getDb();
        
        $includeSelfSql = '';
        if(!$includeSelf){
            $includeSelfSql = 'AND t.treeDepth > 0';
        }
     
        $rows = $db->fetch(
            "SELECT p.*, t2.parentId, t.treeDepth 
            FROM $tParent AS p
            JOIN $tTree AS t ON (p.$tPrimary = t.parentId)
            JOIN $tTree AS t2 ON (p.$tPrimary = t2.childId AND t2.treeDepth=1)
            WHERE t.childId = ? $includeSelfSql 
            ORDER BY t.treeDepth $sort",
            array($id)
        );        
        return $rows;
    }

    public function getParent($id){
        $tParent  = $this->getParentTableName();
        $tTree    = $this->getClosureTableName();
        $tPrimary = $this->getParentTablePrimary();
        $tOrder   = $this->getParentTableOrder();
        $db       = $this->getDb();
        
        $row = $db->fetchRow(
            "SELECT p.*, t2.parentId, t.treeDepth 
            FROM $tParent AS p
            JOIN $tTree AS t ON (p.$tPrimary = t.parentId)
            JOIN $tTree AS t2 ON (p.$tPrimary = t2.childId AND t2.treeDepth=1)
            WHERE t.childId=?
            AND t.treeDepth=1",
            array($id)
        );
        return $row;
    }


}